package com.example.dao;

import com.example.entity.decrease;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class decreaseDaoImpl implements decreaseDao {

    private final Connection connection;

    public decreaseDaoImpl() throws SQLException {
        this.connection = getConnection();
    }

    @Override
    public boolean insertDecreaseData(decrease item) throws SQLException {
        String sql = "INSERT INTO decrease (name, price, decrease_amount, decrease, date) VALUES (?, ?, ?, ?, ?)";
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setString(1, item.getName());
            stmt.setBigDecimal(2, item.getPrice());
            stmt.setBigDecimal(3, item.getDecreaseAmount());
            stmt.setBigDecimal(4, item.getDecreasePercentage());
            stmt.setDate(5, new java.sql.Date(item.getDate().getTime()));

            return stmt.executeUpdate() > 0;
        }
    }

    //  10. 判断该名称是否存在
    @Override
    public boolean existsByName(String name) throws SQLException {
        String sql = "SELECT COUNT(*) FROM decrease WHERE name = ?";
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setString(1, name);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1) > 0; // 如果 count > 0 返回 true
                }
            }
        }
        return false;
    }



    @Override
    public List<decrease> getTodayDecreaseByAmount() throws SQLException {
        return queryDecreaseList(
                "SELECT name, price, decrease_amount AS decreaseAmount, " +
                        "decrease AS decreasePercentage, date " +
                        "FROM decrease WHERE date = CURDATE() " +
                        "ORDER BY decrease_amount DESC LIMIT 10"
        );
    }

    @Override
    public List<decrease> getYesterdayDecreaseByAmount() throws SQLException {
        return queryDecreaseList(
                "SELECT name, price, decrease_amount AS decreaseAmount, " +
                        "decrease AS decreasePercentage, date " +
                        "FROM decrease WHERE date = DATE_SUB(CURDATE(), INTERVAL 1 DAY) " +
                        "ORDER BY decrease_amount DESC LIMIT 10"
        );
    }

    @Override
    public List<decrease> getThisWeekDecreaseByAmount() throws SQLException {
        return queryDecreaseList(
                "SELECT name, price, decrease_amount AS decreaseAmount, " +
                        "decrease AS decreasePercentage, date " +
                        "FROM decrease WHERE YEARWEEK(date, 1) = YEARWEEK(CURDATE(), 1) " +
                        "ORDER BY decrease_amount DESC LIMIT 10"
        );
    }

    @Override
    public List<decrease> getThisMonthDecreaseByAmount() throws SQLException {
        return queryDecreaseList(
                "SELECT name, price, decrease_amount AS decreaseAmount, " +
                        "decrease AS decreasePercentage, date " +
                        "FROM decrease WHERE YEAR(date) = YEAR(CURDATE()) AND MONTH(date) = MONTH(CURDATE()) " +
                        "ORDER BY decrease_amount DESC LIMIT 10"
        );
    }


    @Override
    public List<decrease> getTodayDecreaseByPercentage() throws SQLException {
        return queryDecreaseList(
                "SELECT name, price, decrease_amount AS decreaseAmount, " +
                        "decrease AS decreasePercentage, date " +
                        "FROM decrease WHERE date = CURDATE() " +
                        "ORDER BY decrease DESC LIMIT 10"
        );
    }

    @Override
    public List<decrease> getYesterdayDecreaseByPercentage() throws SQLException {
        return queryDecreaseList(
                "SELECT name, price, decrease_amount AS decreaseAmount, " +
                        "decrease AS decreasePercentage, date " +
                        "FROM decrease WHERE date = DATE_SUB(CURDATE(), INTERVAL 1 DAY) " +
                        "ORDER BY decrease DESC LIMIT 10"
        );
    }

    @Override
    public List<decrease> getThisWeekDecreaseByPercentage() throws SQLException {
        return queryDecreaseList(
                "SELECT name, price, decrease_amount AS decreaseAmount, " +
                        "decrease AS decreasePercentage, date " +
                        "FROM decrease WHERE YEARWEEK(date, 1) = YEARWEEK(CURDATE(), 1) " +
                        "ORDER BY decrease DESC LIMIT 10"
        );
    }

    @Override
    public List<decrease> getThisMonthDecreaseByPercentage() throws SQLException {
        return queryDecreaseList(
                "SELECT name, price, decrease_amount AS decreaseAmount, " +
                        "decrease AS decreasePercentage, date " +
                        "FROM decrease WHERE YEAR(date) = YEAR(CURDATE()) AND MONTH(date) = MONTH(CURDATE()) " +
                        "ORDER BY decrease DESC LIMIT 10"
        );
    }

    private List<decrease> queryDecreaseList(String sql) throws SQLException {
        List<decrease> list = new ArrayList<>();
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                decrease item = new decrease();
                item.setName(rs.getString("name"));
                item.setPrice(rs.getBigDecimal("price"));
                item.setDecreaseAmount(rs.getBigDecimal("decreaseAmount"));
                item.setDecreasePercentage(rs.getBigDecimal("decreasePercentage"));
                item.setDate(rs.getDate("date"));
                list.add(item);
            }
        }
        return list;
    }
}


